/* Note that oracle jdbc driver needs to be in GROOVY_HOME/lib for this to work */
import groovy.transform.ToString;
import groovy.sql.Sql;

Node root = new Node();
/* Properties that need to be edited - starts */
def user = '' // database user name
def pass = '' // database password
def url = 'jdbc:oracle:thin:@server:port:sid' // database connection string
root.owner = 'SCHEMA' // SCHEMA Name of the table
root.tableName = 'TABLE' // Table name from where you want to delete the rows
root.primaryKeyColumn = 'ID' // Primary Key of the table
deleteClause = " = 1234" // For e.g. ID = 1234 The where clause for the primaryKey in the table to be deleted  
/* Properties that need to be edited - ends*/


sql = Sql.newInstance(url, user, pass, "oracle.jdbc.OracleDriver")

constraintQuery = """\
SELECT d.constraint_name, dc.column_name
FROM dba_constraints d, dba_cons_columns dc
WHERE  d.owner = dc.owner
AND d.table_name = dc.table_name
AND d.constraint_name = dc.constraint_name
AND d.constraint_type = 'P'
AND d.owner = ? 
AND d.table_name = ?
"""

constraintReferencesQuery = """\
SELECT d.owner, d.table_name, dc.column_name
FROM dba_constraints d,  dba_cons_columns dc
WHERE d.owner = dc.owner
AND d.table_name = dc.table_name
AND d.constraint_name = dc.constraint_name
AND d.owner = dc.owner
AND d.constraint_type = 'R' 
AND d.r_constraint_name = ?
AND d.r_owner = ?
"""

i=0

buildTree(root)

walkTree(root)

def walkTree (node) {
	if (node.children == null || node.children.size() == 0) {
		generateNodeSql(node)
		return
	}
	
	def childrenNodes = node.children
	
	for (int i = 0 ; i<childrenNodes.size(); i++) {
		def childNode = childrenNodes[i]
		walkTree(childNode)
	}
	generateNodeSql(node)
}

def generateNodeSql (node) {
	def tableName = node.tableName
	def fkColumn = node.foreignKeyColumn
	def fromClause = ""
	def whereClause = " where " 
	def parentNullDeleteQuery = "delete from " + node.owner + "." + node.tableName + " " + node.alias	+ " where "
	def deleteQuery = "delete from " + node.owner + "." + node.tableName + " " + node.alias	+ " where exists ( select 1 from "

	parentNode = node.parent
	
	if (parentNode == null) {
		println parentNullDeleteQuery + node.alias + "." + node.primaryKeyColumn + deleteClause + ";"
		return
	}
	
	while (parentNode != null) {
		fromClause = fromClause + parentNode.owner + "." + parentNode.tableName + " " + parentNode.alias +","
		whereClause = whereClause + node.alias + "." + node.foreignKeyColumn + " = " + parentNode.alias + "." + parentNode.primaryKeyColumn + " and " 
		node = parentNode
		parentNode = parentNode.parent
	}

	def finalWhereClause = node.alias + "." + node.primaryKeyColumn + deleteClause
	
	// Removing last "," from the fromClause
	if (fromClause != null && fromClause.length() > 0) {
		fromClause = fromClause[0..-2]
	}
	
	if (whereClause != null && whereClause.length() > 7) {
		whereClause = whereClause[0..-5]
	}
	
	println deleteQuery + fromClause + " " +  whereClause + "and " + finalWhereClause + ");"
}

def buildTree (root) {
	def pkName = getPrimaryKeyConstraint(root)
	root.alias = "t"+i
	i++
	
	if (pkName == null) {
		return
	}
	
	def nodes = getChilderConstraints(pkName, root.owner)
	
	if (nodes == null || nodes.size() == 0) {
		return
	}
	
	root.children = nodes
	
	for (int i; i<nodes.size(); i++)
	{
		node = nodes.get(i)
		node.parent = root;
		buildTree(node)
	}
}

def getChilderConstraints(pkName, owner) {
	nodes = []
	sql.eachRow(constraintReferencesQuery, [pkName, owner]) {
		Node node = new Node()
		node.owner = it.owner
		node.tableName = it.table_name
		node.foreignKeyColumn = it.column_name
		nodes.add(node) 
	}
	return nodes
}

def getPrimaryKeyConstraint(node) {
	def constraintName = null
	sql.eachRow(constraintQuery, [node.owner,node.tableName]) {
		constraintName = it.constraint_name;
		node.primaryKeyColumn = it.column_name
	}

	//println "Building Subtree for : " + node.owner+ "." + node.tableName + " Constraint Name : " + constraintName
	return constraintName
}

@ToString(includeNames=true)
class Node
{
	/** Schema owner */
	String owner;
	
	/** Table Name */
	String tableName
	
	/** Primary Key of the table */
	String primaryKeyColumn
	
	/** Column which refers to the parent table */ 
	String foreignKeyColumn
	
	Node parent
	
	List<Node> children
	
	String alias
}

